package com.portalEsporteClubeBahia.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.portalEsporteClubeBahia.app.ApplicationException;
import com.portalEsporteClubeBahia.app.vo.TeamVO;

public class TeamDAO extends DAOFactory {

	public TeamDAO() throws ApplicationException {
		super();
	}
	
	public TeamVO findByPrimaryKey(TeamVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TeamVO team = new TeamVO();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME, SHORTNAME, URLIMAGE FROM TBL_TEAM WHERE ID = ?");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, filter.getId());
			rs = ps.executeQuery();
			
			while(rs.next()){
				team.setId(rs.getInt("ID"));
				team.setName(rs.getString("NAME"));
				team.setShortName(rs.getString("SHORTNAME"));
				team.setUrlImage(rs.getString("URLIMAGE"));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return team;
		
	}
	
	public TeamVO findByShortNameId(TeamVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TeamVO team = new TeamVO();
		
		StringBuffer sql = new StringBuffer("SELECT * FROM TBL_TEAM WHERE ID != ? AND SHORTNAME = ?");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, filter.getId());
			ps.setString(2, filter.getShortName());
			rs = ps.executeQuery();
			
			while(rs.next()){
				team.setId(rs.getInt("ID"));
				team.setName(rs.getString("NAME"));
				team.setShortName(rs.getString("SHORTNAME"));
				team.setUrlImage(rs.getString("URLIMAGE"));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return team;
		
	}
	
	public List<TeamVO> findAll() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<TeamVO> list = new ArrayList<TeamVO>();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME, SHORTNAME, URLIMAGE FROM TBL_TEAM ORDER BY NAME");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				TeamVO team = new TeamVO();
				team.setId(rs.getInt("ID"));
				team.setName(rs.getString("NAME"));
				team.setShortName(rs.getString("SHORTNAME"));
				team.setUrlImage(rs.getString("URLIMAGE"));
				list.add(team);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public List<TeamVO> findByFilter(TeamVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<TeamVO> list = new ArrayList<TeamVO>();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME, SHORTNAME, URLIMAGE FROM TBL_TEAM ");
		sql.append(" WHERE 1=1 ");
		if(filter.getName()!=null && !filter.getName().equalsIgnoreCase(""))
			sql.append(" AND NAME LIKE '%"+filter.getName()+"%' ");
		if(filter.getShortName()!=null && !filter.getShortName().equalsIgnoreCase(""))
			sql.append(" AND SHORTNAME LIKE '%"+filter.getShortName()+"%' ");
		sql.append(" ORDER BY NAME ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				TeamVO team = new TeamVO();
				team.setId(rs.getInt("ID"));
				team.setName(rs.getString("NAME"));
				team.setShortName(rs.getString("SHORTNAME"));
				team.setUrlImage(rs.getString("URLIMAGE"));
				list.add(team);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public void insert(TeamVO team) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("INSERT INTO TBL_TEAM (NAME, SHORTNAME, URLIMAGE) VALUES (?,?,?) ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, team.getName());
			ps.setString(2, team.getShortName());
			ps.setString(3, team.getUrlImage());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(TeamVO team) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("UPDATE TBL_TEAM SET NAME=?, SHORTNAME=?, URLIMAGE=? WHERE ID =? ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, team.getName());
			ps.setString(2, team.getShortName());
			ps.setString(3, team.getUrlImage());
			ps.setInt(4, team.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(TeamVO team) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("DELETE FROM TBL_TEAM WHERE ID =? ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, team.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}

}
